# 查询 mysql 数据库 表 t_five_status 中 f_text like %复制打开抖音，看看% 的数据，只查询一条
# 调用方法，获取到json 包含分享信息 描述与视频地址
# 视频描述更新到数据库表 t_five_status 中 f_text 字段
# 通过视频地址，上传视频地址中的视频到 oss 上，返回 oss 上的视频地址
# 将 oss 上的视频地址 回写到表 t_five_page_info 中，并插入一条记录
import time

import pymysql
import json
import requests
import oss2

from douyin_download import DouyinDownload

# 数据库连接配置
db_config = {
    'host': 'rm-m5emupbnci0imglg32o.mysql.rds.aliyuncs.com',
    'user': 'drebander',
    'password': 'Ds152278',
    'database': 'ry_plus'
}

# 阿里云 OSS 配置
oss_config = {
    'access_key_id': 'LTAI4G2FFKwsh3dExGFFzFDs',
    'access_key_secret': '6EEBnuJxNOdlB7kxwjFipnFu7pp4Jn',
    'bucket_name': 'dre-five',
    'endpoint': 'oss-cn-qingdao.aliyuncs.com'
}


# 1. Database Handler 类：负责数据库操作
class DatabaseHandler:
    def __init__(self, config):
        self.config = config

    def fetch_one_five_status(self):
        query = "SELECT * FROM t_five_status WHERE f_text LIKE '%复制打开抖音，看看%' and f_text like '%https%' LIMIT 1"
        connection = pymysql.connect(**self.config)

        try:
            with connection.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchone()
                if result:
                    columns = [col[0] for col in cursor.description]
                    return dict(zip(columns, result))
                return None
        finally:
            connection.close()

    def insert_url_struct(self, page_id, status_id, short_url):
        query = """
            INSERT INTO t_url_struct ( f_object_type, f_page_id, f_status_id, f_short_url, f_url_type)
            VALUES ('video', %s, %s, %s, 39)
        """
        connection = pymysql.connect(**self.config)

        try:
            with connection.cursor() as cursor:
                cursor.execute(query, (page_id, status_id, short_url))
                connection.commit()
        finally:
            connection.close()

    def insert_page_info(self, media_id, status_id):
        query = """
               INSERT INTO t_page_info ( f_media_id, f_status_id, f_object_type, f_type)
               VALUES ( %s, %s, 'video', 11)
           """
        connection = pymysql.connect(**self.config)

        try:
            with connection.cursor() as cursor:
                cursor.execute(query, (media_id, status_id))
                connection.commit()
                return cursor.lastrowid  # 返回生成的 f_id
        finally:
            connection.close()

    def insert_media_info(self, stream_url):
        query = """
            INSERT INTO t_media_info ( f_stream_url)
            VALUES ( %s)
        """
        connection = pymysql.connect(**self.config)

        try:
            with connection.cursor() as cursor:
                cursor.execute(query, (stream_url,))
                connection.commit()
                return cursor.lastrowid  # 返回生成的 f_id
        finally:
            connection.close()

    def update_five_status(self, record_id, new_description):
        query = "UPDATE t_five_status SET f_text = %s WHERE f_id = %s"
        connection = pymysql.connect(**self.config)

        try:
            with connection.cursor() as cursor:
                cursor.execute(query, (new_description, record_id))
                connection.commit()
        finally:
            connection.close()

    def insert_page_info(self, media_id, status_id):
        query = """
            INSERT INTO t_page_info ( f_media_id, f_status_id, f_object_type, f_type)
            VALUES ( %s, %s, 'video', 11)
        """
        connection = pymysql.connect(**self.config)

        try:
            with connection.cursor() as cursor:
                cursor.execute(query, (media_id, status_id))
                connection.commit()
                return cursor.lastrowid  # 返回生成的 f_id
        finally:
            connection.close()


# 2. VideoInfoParser 类：负责解析 f_text 中的视频信息
class VideoInfoParser:
    def __init__(self, record):
        self.record = record

    def parse_video_info(self):
        f_text = self.record['f_text']
        try:
            # 字符串截取，】之后，https 之前的字符串 f_text
            desc = f_text[f_text.find('】') + 1:f_text.find('https')]
            douyin = DouyinDownload(f_text)
            data = douyin.download_share()
            return desc, data['aweme']
        except json.JSONDecodeError:
            print("Error decoding JSON from f_text")
            return None, None


# 3. OSSUploader 类：负责上传视频到阿里云 OSS
class OSSUploader:
    def __init__(self, config):
        self.config = config
        self.auth = oss2.Auth(config['access_key_id'], config['access_key_secret'])
        self.bucket = oss2.Bucket(self.auth, config['endpoint'], config['bucket_name'])

    def upload_video(self, video_url):
        response = requests.get(video_url)
        if response.status_code == 200:
            video_content = response.content
            # 当前时间戳作为视频名
            video_name = f"{int(time.time())}.mp4"
            self.bucket.put_object(video_name, video_content)
            oss_url = f"https://{self.config['bucket_name']}.{self.config['endpoint']}/{video_name}"
            print(f"Uploaded video to OSS: {oss_url}")
            return oss_url
        else:
            print(f"Failed to download video. Status code: {response.status_code}")
            return None


# 4. MainProcessor 类：负责整个流程的控制
class MainProcessor:
    def __init__(self, db_config, oss_config):
        self.db_handler = DatabaseHandler(db_config)
        self.oss_uploader = OSSUploader(oss_config)

    def process(self):
        # Step 1: Fetch fiveStatus from t_five_status
        fiveStatus = self.db_handler.fetch_one_five_status()
        if not fiveStatus:
            print("No fiveStatus found.")
            return

        # Step 2: Parse video information from the fiveStatus
        parser = VideoInfoParser(fiveStatus)
        video_description, video_url = parser.parse_video_info()

        if not video_description or not video_url:
            print("Failed to parse video information.")
            return

        # Step 3: Update description back to the database
        self.db_handler.update_five_status(fiveStatus['f_id'], video_description)
        print(f"Updated f_text in t_five_status for fiveStatus ID {fiveStatus['f_id']}.")

        # Step 4: Upload the video to OSS
        oss_video_url = self.oss_uploader.upload_video(video_url)
        if not oss_video_url:
            print("Video upload failed.")
            return

        # Step 5: Insert a new record into t_media_info
        media_id = self.db_handler.insert_media_info(oss_video_url)
        print(f"Inserted new record into t_media_info with ID {media_id}.")
        # Step 6: Insert a new record into t_page_info
        page_id = self.db_handler.insert_page_info(media_id, fiveStatus['f_id'])
        print(f"Inserted new record into t_page_info with ID {page_id}.")
        # Step 7: Insert a new record into t_url_struct
        self.db_handler.insert_url_struct(page_id, fiveStatus['f_id'], oss_video_url)
        print("Inserted new record into t_url_struct.")


def main():
    processor = MainProcessor(db_config, oss_config)
    processor.process()


if __name__ == '__main__':
    main()
